﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'roleFindByUser')
	BEGIN
		DROP  Procedure  roleFindByUser
	END
GO

-- =============================================
-- Author:		Ricardo Sosa
-- Create date: 07/03/2009
-- Description:	Gets the roles of the specified user
-- =============================================
CREATE PROCEDURE [dbo].[roleFindByUser]
	@usr_name VARCHAR(50),
	@app_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @usr_id INT;

	SELECT @usr_id = lit_usuario_id
	FROM lit_usuario
	WHERE lit_usuario_username = @usr_name
	AND lit_aplicacion_id = @app_id;

	IF (@usr_id IS NOT NULL) BEGIN

		SELECT r.* 
		FROM lit_rol r
		JOIN lit_usuario_rol ur ON ur.lit_rol_id = r.lit_rol_id
		WHERE r.lit_aplicacion_id = @app_id
		AND ur.lit_usuario_id = @usr_id
		ORDER BY r.lit_rol_nombre;

	END
	
END
GO